<?php

function beneficiary_wise(){ /* Function to list Beneficiraies*/
 global $base_url;
 global $language;
 if($language->language=='ta'){
	$lang= '/ta';
}else{
 $lang='';
}	
 //$output = '<div class="rsslogo">' . get_rss_feed("scheme_rssfeed") . '</div>	';
 $output .= '<div id="viewmod_schemes_tab_'.$language->language.'">
		<div class="menu_tab view-header">
			<div class="department_wise">
				<a class="inactive" href="'.$base_url.$lang.'/scheme/department_wise/">'.t('Department wise').'</a>		
			</div>
		    <div class="beneficiary_wise">				
				<a class="view_head_sel"  href="'.$base_url.$lang.'/scheme/beneficiary_wise">'.t('Beneficiary Wise').'</a>
			</div>		
			<div class="atoz_view">				
				<a class="inactive" href="'.$base_url.$lang.'/scheme/alpha_view">'.t('A to Z').'</a>
			</div>		
		    <div class="search">				
				<a class="inactive"  href="'.$base_url.$lang.'/scheme/search">'.t('Search').'</a>
			</div>
		</div>		
		<div class="clear"></div>		
	</div>';
	
	my_db_change(1);
	$result = db_query("SELECT distinct id,beneficiaries_name FROM scheme_beneficiaries ORDER BY beneficiaries_name ASC"); 
	my_db_change(0);
		 $i=0; 
	  $output .= '<div class="result_inner">';	  
	  foreach($result as $item) {	  
			if($language->language== 'ta'){	  
				$output .= '<div class="scheme_list_category"><p><a  href="'.$base_url.'/'.$language->language.'/scheme/beneficiary_wise/'.$item->id.'">'.t(substr($item->beneficiaries_name,0,255)).'</a></p></div>'; 		}
			else {
				$output .= '<div class="scheme_list_category"><p><a  href="'.$base_url.'/scheme/beneficiary_wise/'.$item->id.'">'.t(substr($item->beneficiaries_name,0,255)).'</a></p></div>'; 	}	
				$i++;
	  }
	  if($i == '0'){
			 $output .='<div class="no_record">'.t('No Results Found').' </div>';
			}	
	  $output .= '</div>';	
	  
	$form['field'] = array(
			'#type' => 'fieldset',
			'#title' => '',
			'#description' => $output,
			'#tree' => TRUE,
		);
	return $form;
}
function beneficiary_wise_view($benf_id=''){  /* Function to list Beneficiraies DEpartment Title*/
global $base_url;
global $language;
 if($language->language=='ta'){
	$lang= '/ta';
}else{
 $lang='';
}
 	 //$output = '<div class="rsslogo">' . get_rss_feed("scheme_rssfeed") . '</div>	';
 	 $output .= '<div id="viewmod_schemes_tab_'.$language->language.'">
		<div class="menu_tab view-header">
			<div class="department_wise">
				<a class="inactive" href="'.$base_url.$lang.'/scheme/department_wise/">'.t('Department wise').'</a>		
			</div>
		    <div class="beneficiary_wise">				
				<a class="view_head_sel"  href="'.$base_url.$lang.'/scheme/beneficiary_wise">'.t('Beneficiary Wise').'</a>
			</div>		
			<div class="atoz_view">				
				<a class="inactive" href="'.$base_url.$lang.'/scheme/alpha_view">'.t('A to Z').'</a>
			</div>		
		    <div class="search">				
				<a class="inactive"  href="'.$base_url.$lang.'/scheme/search">'.t('Search').'</a>
			</div>
		</div>		
		<div class="clear"></div>		
	</div>';
	
	if(!empty($benf_id)){
			$benf_cond = " AND b.field_scheme_beneficiaries_value = '" . (string)$benf_id . "'";
		}
		
	my_db_change(1);
	$sql = db_query("SELECT d.beneficiaries_name FROM node AS a, field_data_field_scheme_concern_department AS b, depart_mst AS c, scheme_beneficiaries AS 				     
	d,field_data_field_scheme_beneficiaries as sb WHERE a.nid = b.entity_id AND a.vid = b.revision_id and a.nid = sb.entity_id AND a.vid = sb.revision_id AND c.dep_id = CAST(b.field_scheme_concern_department_value AS INTEGER)
	AND a.status = 1 AND d.id = CAST(field_scheme_beneficiaries_value AS INT) AND Cast(sb.field_scheme_beneficiaries_value as INT) =$benf_id AND a.language ='".$language->language.		 	"'  ORDER BY d.beneficiaries_name")->fetchField();
	
	/*$sql = db_query("SELECT c.dept_name 
				FROM node AS a, content_type_scheme AS b, depart_mst AS c, scheme_beneficiaries AS d
				WHERE a.nid = b.nid AND a.vid = b.vid AND c.dep_id = CAST(b.field_scheme_concern_department_value AS INTEGER)
				AND a.status = 1 AND d.id = CAST(field_scheme_beneficiaries_value AS INT) AND Cast(b.field_scheme_beneficiaries_value as INT) = $benf_id
				AND a.language ='".$language->language."' ORDER BY d.beneficiaries_name")->fetchField();
				
	$query = db_select('node', 'a');
	$query->join('content_type_scheme', 'b', 'a.nid =b.nid AND a.vid =b.vid  AND  b.field_scheme_beneficiaries_value = \'' .(string)$benf_id .'\'');
	$query->join('depart_mst', 'c','CAST(b.field_scheme_concern_department_value as int)=  c.dep_id');
	$query->join('scheme_beneficiaries', 'd','CAST(b.field_scheme_beneficiaries_value as int)=  d.id');
	$query->condition('a.language',$language->language);
	//$query->condition('CAST(b.field_scheme_beneficiaries_value as INT)','$benf_id');
	$query->condition('a.status','1');
	$query->fields('a',array('title','nid','vid'))->fields('b',array('field_scheme_concern_department_value','nid','vid'))
	->fields('c',array('dep_id','dept_name'))->fields('d',array('id','beneficiaries_name'))
	->orderBy('a.title', 'ASC')->distinct('a.title','c.dep_id','a.nid','b.nid','b.field_scheme_description_value','d.beneficiaries_name');
	$query_result=$query->extend('PagerDefault')->limit(15)->execute();	
	//print_r( $query_result);
		$query_result=db_query("SELECT DISTINCT a.title AS title, a.nid AS nid, a.vid AS vid, b.field_scheme_concern_department_value AS field_scheme_concern_department_value, 
	b.entity_id AS b_nid, b.revision_id AS b_vid, c.dep_id AS dep_id, c.dept_name AS dept_name, d.id AS id,
	 d.beneficiaries_name AS beneficiaries_name FROM node a 
	INNER JOIN field_data_field_scheme_concern_department b ON a.nid =b.entity_id AND a.vid =b.revision_id 
		INNER JOIN  field_data_field_scheme_beneficiaries as sc on sc.field_scheme_beneficiaries_value = '$benf_id' and a.nid=sc.entity_id and a.vid=sc.revision_id
		INNER JOIN depart_mst c ON CAST(b.field_scheme_concern_department_value as int)= c.dep_id 
		INNER JOIN scheme_beneficiaries d ON CAST(sc.field_scheme_beneficiaries_value as int)= d.id 
		WHERE (a.language = 'en') AND (a.status = 1)
		 ORDER BY a.title ASC OFFSET 0");
*/
	$query = db_select('node', 'a');
	$query->join('field_data_field_scheme_beneficiaries', 'sc','sc.field_scheme_beneficiaries_value = \'' .(string)$benf_id .'\' and a.nid=sc.entity_id and a.vid=sc.revision_id');
	$query->join('scheme_beneficiaries', 'd','CAST(sc.field_scheme_beneficiaries_value as int)=  d.id');
	$query->condition('a.language',$language->language);
	$query->condition('a.status','1');
	$query->condition('a.type','scheme');
	$query->fields('a',array('title','nid','vid'))
	->fields('d',array('id','beneficiaries_name'))->fields('sc',array('field_scheme_beneficiaries_value'))
	->orderBy('a.title', 'ASC')->distinct('a.title','c.dep_id','a.nid','b.nid','b.field_scheme_description_value','d.beneficiaries_name');
	$query_result=$query->extend('PagerDefault')->limit(15)->execute();	
	//print_r( $query_result);
	my_db_change(0);
	$output .= '<div class="result_inner">';
	$output .= '<div class="heading_name"><h3>'.t(substr($sql,0,255)).'</h3></div>'; 
	$i=0;	
	foreach($query_result as $row){
		if($language->language== 'ta'){	  
				 $output .= '<div class="scheme_list"><p><a  href="'.$base_url.'/'.$language->language.'/scheme/data_view/'.$row->nid.'">'.substr($row->title,0,255).'</a></p></div>';
		}
		else {
		 $output .= '<div class="scheme_list"><p><a  href="'.$base_url.'/scheme/data_view/'.$row->nid.'">'.substr($row->title,0,255).'</a></p></div>';
		 }
		 $i++;
	}
	if($i == '0'){
			 $output .='<div class="no_record">'.t('No Results Found').' </div>';
			}	
	$output .= theme('pager');	  
	$output .= '</div>';
	$form['field'] = array(
			'#type' => 'fieldset',
			'#title' => '',
			'#description' => $output,
			'#tree' => TRUE,
		);
	return $form;
}
function alpha_view($alpha){  /* Function to Sort according to alphabets*/
global $base_url;
global $language;
 if($language->language=='ta'){
	$lang= '/ta';
}else{
 $lang='';
}
 	// $output = '<div class="rsslogo">' . get_rss_feed("scheme_rssfeed") . '</div>	';
	 $output .= '<div id="viewmod_schemes_tab_'.$language->language.'">
		<div class="menu_tab view-header">
			<div class="department_wise">
				<a class="inactive" href="'.$base_url.$lang.'/scheme/department_wise/">'.t('Department wise').'</a>		
			</div>
		    <div class="beneficiary_wise">				
				<a class="inactive"  href="'.$base_url.$lang.'/scheme/beneficiary_wise">'.t('Beneficiary Wise').'</a>
			</div>		
			<div class="atoz_view">				
				<a class="view_head_sel" href="'.$base_url.$lang.'/scheme/alpha_view">'.t('A to Z').'</a>
			</div>		
		    <div class="search">				
				<a class="inactive"  href="'.$base_url.$lang.'/scheme/search">'.t('Search').'</a>
			</div>
		</div>		
		<div class="clear"></div>		
	</div>';
	my_db_change(1);
	$qry = "SELECT a.title, b.nid FROM node AS a, content_type_scheme AS b
			WHERE a.nid = b.nid AND a.vid = b.vid AND a.status = 1 ORDER BY trim(a.title)";
			
	$val= strtoupper($alpha);
	if($val != 'ALL'){
			$cond = " AND UPPER(a.title) LIKE '" . $val ."%'";
	}	
	
	$query = db_select('node','a');
		$query->join('field_data_field_scheme_concern_department','b','a.nid = b.entity_id AND a.vid = b.revision_id AND a.status = 1'.$cond .'');
		$query->fields('a',array('nid','title','vid'));
		$query->condition('a.language',$language->language);
		$query->condition('a.type','scheme');
		$query->orderBy('title','ASC');
		$query->distinct('title','nid');
	$query_result=$query->extend('PagerDefault')->limit(12)->execute();	
	
	my_db_change(0);
	//print_r($query_result);
	$output .= '<div class="result_inner">';
	$output .=get_ATOZ($alpha);
	$i=0;
	foreach($query_result as $row){
	if($language->language== 'ta'){	  
		$output .= '<div class="scheme_list"><p><a  href="'.$base_url.'/'.$language->language.'/scheme/data_view/'.$row->nid.'">'.substr($row->title,0,255).'</a></p></div>';
	}
	else {
		 $output .= '<div class="scheme_list"><p><a  href="'.$base_url.'/scheme/data_view/'.$row->nid.'">'.substr($row->title,0,255).'</a></p></div>'; 
	}
	$i++;
	}
	if($i == '0'){
			 $output .='<div class="no_record">'.t('No Results Found').' </div>';
			}	
	$output .= theme('pager');	  
	
	$output .= '</div>';
	return $output;
}
function get_ATOZ($alpha){ /* Function to display alphabets*/
global $base_url;
		$list = '<div class = "alpha_list view-header">';
		for($i =65; $i < 91; $i++){
			$class = "";
			if(chr($i) == $alpha){
				$class = " view_head_sel";
			}
			else
			{
			$class = " view_head_nonsel";
			}
		    $list .= '<a class = "myclass' . $class . '"  href = "' .$base_url.'/scheme/alpha_view/'. chr($i) .'" title = "' . chr($i) .'" id = "' . chr($i) .'">' . chr($i) . '</a>';//   l(chr($i), chr($i), array('attributes' => array('class' => 'myclass', 'title' => t(chr($i)), 'id' => chr($i))));
		}
		$class = "";
		if($alpha == 'All' || empty($alpha)){
			$class = " view_head_sel";
		}
		$list .= '<a class = "myclass' . $class . '" href = "' .$base_url.'/scheme/alpha_view/All" title = "All" id = "All"> All </a>';
		$list .= '</div>';
	    return $list;
}
function scheme_search_view(){
global $base_url;
global $language;
 if($language->language=='ta'){
	$lang= '/ta';
}else{
 $lang='';
}
 	 //$output = '<div class="rsslogo">' . get_rss_feed("scheme_rssfeed") . '</div>	';
 	 $output .= '<div id="viewmod_schemes_tab_'.$language->language.'">
		<div class="menu_tab view-header">
			<div class="department_wise">
				<a class="inactive" href="'.$base_url.$lang.'/scheme/department_wise/">'.t('Department wise').'</a>		
			</div>
		    <div class="beneficiary_wise">				
				<a class="inactive"  href="'.$base_url.$lang.'/scheme/beneficiary_wise">'.t('Beneficiary Wise').'</a>
			</div>		
			<div class="atoz_view">				
				<a class="inactive" href="'.$base_url.$lang.'/scheme/alpha_view">'.t('A to Z').'</a>
			</div>		
		    <div class="search">				
				<a class="view_head_sel"  href="'.$base_url.$lang.'/scheme/search">'.t('Search').'</a>
			</div>
		</div>		
		<div class="clear"></div>		
	</div>';
	$output .= '<div class="result_inner">';
	$output .= '<div><div id="edit-scheme-name-wrapper" class="form-item">';
 	$output .= '<label for="edit-scheme-name">'.t('Search').': <span title="This field is required." class="form-required">*</span></label>';
 	$output .= '<input type="text" class="form-text required" value="" size="20" id="edit-scheme-name" name="scheme_name" maxlength="128">';
	$output .= '<div class="sch-description">Ex: Education OR Agmark</div>';
	$output .= '</div>';
	$output .= '<input type="button" class="form-submit" value="'.t('Search').'" name="search" onclick="return schemesearch()">';
	$output .= '</div>';
	$output .= '<div class="result_out"></div></div>';
	$output .= theme('pager');
	$form['field'] = array(
			'#type' => 'fieldset',
			'#title' => '',
			'#description' => $output,
			'#tree' => TRUE,
		);
	return $form;
}
?>